﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

using Aoxuelf.MDSTool.Model;
namespace Aoxuelf.MDSTool.SQL
{
    public class QuerySQLParser
    {
        private const string SK_Select = "select";
        private const string SK_From = "from";
        private const string SC_Start = "/*";
        private const string SC_End = "*/";

        public static QuerySQLParseInfo Parse(string text)
        {
            if (string.IsNullOrEmpty(text))
            {
                return null;
            }

            if (text.IndexOf(SK_Select, StringComparison.CurrentCultureIgnoreCase) < 0)
            {
                return null;
            }

            if (text.IndexOf(SK_From, StringComparison.CurrentCultureIgnoreCase) < 0)
            {
                return null;
            }

            QuerySQLParseInfo parseInfo = new QuerySQLParseInfo();
            ParseColumns(parseInfo, text);

            IList<SQLParamInfo> lst = ParseParams(text);
            foreach (SQLParamInfo item in lst)
            {
                foreach (ColumnInfo column in parseInfo.SelectColumns)
                {
                    if (item.Name.IndexOf(column.ColumnName, StringComparison.CurrentCultureIgnoreCase) >= 0)
                    {
                        item.Text = column.ColumnText;
                        break;
                    }
                }
                
                parseInfo.SQLParams.Add(item);
            }

            return parseInfo;
        }

        private static void ParseColumns(QuerySQLParseInfo parseInfo, string text)
        {
            int selectIndex = text.IndexOf(SK_Select, StringComparison.CurrentCultureIgnoreCase);
            int fromIndex = text.IndexOf(SK_From, StringComparison.CurrentCultureIgnoreCase);
            string selectText = text.Substring(selectIndex + SK_Select.Length, fromIndex - selectIndex - SK_Select.Length);
            string[] columns = selectText.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string columnText in columns)
            {
                if (string.IsNullOrWhiteSpace(columnText))
                {
                    continue;
                }

                string comment = ParseComment(columnText);
                string columnName = ParseColumByComma(ParseColumnBySpace(columnText));
                parseInfo.SelectColumns.Add(new ColumnInfo { ColumnName = columnName, ColumnText = comment });
            }
        }

        private static string ParseColumnBySpace(string text)
        {
            string columnText = regexComment.Replace(text, string.Empty).Replace(",", string.Empty);
            string[] splitText = columnText.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
            if (splitText.Length > 0)
            {
                return splitText[splitText.Length - 1].Trim();
            }
            else
            {
                return columnText.Trim();
            }
        }

        private static string ParseColumByComma(string text)
        {
            string[] splitText = text.Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
            if (splitText.Length > 0)
            {
                return splitText[splitText.Length - 1].Trim();
            }
            else
            {
                return text.Trim();
            }
        }

        private static string ParseComment(string text)
        {
            int indexStart = text.IndexOf(SC_Start);
            if (indexStart > 0)
            {
                int indexEnd = text.IndexOf(SC_End);
                string s = text.Substring(indexStart + SC_Start.Length, indexEnd - indexStart - 2);
                return s;
            }
            else
            {
                return string.Empty;
            }
        }

        private static Regex regexComment = new Regex(@"(/\*.+\*/)", RegexOptions.Compiled | RegexOptions.Multiline);
        private static Regex regexParam = new Regex(@"(#\w+#)", RegexOptions.Compiled | RegexOptions.Multiline);

        public static IList<SQLParamInfo> ParseParams(string text)
        {
            List<string> lstPara = new List<string>();
            List<SQLParamInfo> lst = new List<SQLParamInfo>();
            string[] lines = text.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (var line in lines)
            {
                Match mp = regexParam.Match(line);
                if (mp.Success)
                {
                    if (!lstPara.Contains(mp.Value))
                    {
                        SQLParamInfo item = new SQLParamInfo { Name = mp.Value, Type = "string", Text = string.Empty };
                        lstPara.Add(mp.Value);

                        if (regexComment.IsMatch(line))
                        {
                            item.Text = regexComment.Match(line).Value.Replace("/*", string.Empty).Replace("*/", string.Empty);
                        }

                        lst.Add(item);
                    }
                }
            }

            return lst;
        }
    }
}
